Imports Microsoft.VisualBasic
Imports System.Data
Imports System.Data.SqlClient

Namespace sk.DAL.SQLProviders
    Public Class SQLCategoryProvider
        Inherits CategoryProvider
        Public Overrides Function CreateCategory(ByVal Cat As Category) As Boolean
            Try
                Using con As New SqlConnection(Me.ConnectionString)
                    Dim cmd As New SqlCommand("Category_CreateCategory", con)
                    cmd.CommandType = CommandType.StoredProcedure

                    Dim pCategoryID As New SqlParameter("@ID", SqlDbType.SmallInt)
                    pCategoryID.Value = GetNextID()
                    Dim pName As New SqlParameter("@Name", SqlDbType.NVarChar)
                    pName.Value = Cat.Name


                    cmd.Parameters.Add(pCategoryID)
                    cmd.Parameters.Add(pName)


                    con.Open()
                    cmd.ExecuteNonQuery()
                    Return True
                End Using
            Catch ex As Exception
                Return False
            End Try
        End Function

        Public Overrides Function DeleteCategory(ByVal CatID As Integer) As Boolean
            Try
                Using con As New SqlConnection(Me.ConnectionString)

                    Dim cmd As New SqlCommand("Category_DeleteCategory", con)
                    cmd.CommandType = CommandType.StoredProcedure

                    Dim pCategoryID As New SqlParameter("@ID", SqlDbType.SmallInt)
                    pCategoryID.Value = CatID
                    cmd.Parameters.Add(pCategoryID)


                    con.Open()
                    cmd.ExecuteNonQuery()
                    Return True
                End Using
            Catch ex As Exception
                Return False
            End Try
        End Function

        Public Overloads Overrides Function GetAllCategories() As System.Collections.Generic.List(Of Category)
            Try
                Using con As New SqlConnection(Me.ConnectionString)

                    Dim cmd As New SqlCommand("Category_GetAllCategories", con)
                    cmd.CommandType = CommandType.StoredProcedure
                    con.Open()

                    Dim reader As IDataReader = cmd.ExecuteReader()
                    Return GetCategoryCollectionFromReader(reader)

                End Using
            Catch ex As Exception
                Return New System.Collections.Generic.List(Of Category)
            End Try
        End Function

        Public Overloads Overrides Function GetCategory(ByVal CatID As Integer) As Category
            Try
                Using con As New SqlConnection(Me.ConnectionString)

                    Dim cmd As New SqlCommand("Category_GetCategoryByID", con)
                    cmd.CommandType = CommandType.StoredProcedure

                    Dim pCategoryID As New SqlParameter("@ID", SqlDbType.SmallInt)
                    pCategoryID.Value = CatID
                    cmd.Parameters.Add(pCategoryID)


                    con.Open()
                    Dim reader As SqlDataReader = cmd.ExecuteReader()
                    reader.Read()
                    Dim newCat As New Category
                    newCat.CategoryID = CInt(reader.GetValue(0))
                    newCat.Name = reader.GetString(1)

                    Return newCat
                End Using
            Catch ex As Exception
                Return New Category
            End Try
        End Function

        Public Overrides Function UpdateCategory(ByVal Cat As Category) As Boolean
            Try
                Using con As New SqlConnection(Me.ConnectionString)

                    Dim cmd As New SqlCommand("Category_UpdateCategory", con)
                    cmd.CommandType = CommandType.StoredProcedure

                    Dim pCommentID As New SqlParameter("@ID", SqlDbType.SmallInt)
                    pCommentID.Value = Cat.CategoryID
                    Dim pName As New SqlParameter("@Name", SqlDbType.NVarChar)
                    pName.Value = Cat.Name


                    cmd.Parameters.Add(pCommentID)
                    cmd.Parameters.Add(pName)


                    con.Open()
                    cmd.ExecuteNonQuery()
                    Return True
                End Using
            Catch ex As Exception
                Return False
            End Try
        End Function

        Public Overrides Function GetNextID() As Integer
            Try
                Using con As New SqlConnection(Me.ConnectionString)
                    Dim cmd As New SqlCommand("Category_GetNextID", con)
                    cmd.CommandType = CommandType.StoredProcedure
                    con.Open()
                    Dim dr As SqlDataReader
                    dr = cmd.ExecuteReader
                    Dim newId As Integer
                    dr.Read()
                    newId = CInt(dr.GetValue(0))
                    Return newId + 1
                End Using
            Catch ex As Exception
                Return 0
            End Try
        End Function
    End Class
End Namespace
